"""
数据库管理器

提供数据库连接、会话管理和基本操作。
"""

import os
from typing import Dict, Any, Optional
from pathlib import Path
import logging

from sqlalchemy import create_engine, event, text
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.pool import StaticPool

from src.core.di import Injectable, Inject, Service
from src.models.base import Base


# 全局数据库管理器实例
_database_manager: Optional['DatabaseManager'] = None


@Service("database_manager")
class DatabaseManager:
    """
    数据库管理器
    
    管理SQLite数据库连接、会话和基本操作。
    """
    
    def __init__(self,
                 config: Dict[str, Any] = Inject("config"),
                 logger: logging.Logger = Inject("logger")):
        self.config = config
        self.logger = logger
        
        # 数据库配置
        self.db_config = config.get("database", {})
        self.db_path = self.db_config.get("path", "data/mixvideo.db")
        self.echo = self.db_config.get("echo", False)
        self.pool_size = self.db_config.get("pool_size", 10)
        self.max_overflow = self.db_config.get("max_overflow", 20)
        
        # 确保数据库目录存在
        db_path = Path(self.db_path)
        db_path.parent.mkdir(parents=True, exist_ok=True)
        
        # 创建数据库引擎
        self.engine = None
        self.SessionLocal = None
        self._initialize_database()
        
        # 设置全局实例
        global _database_manager
        _database_manager = self
    
    def _initialize_database(self) -> None:
        """初始化数据库"""
        try:
            # 创建SQLite连接字符串
            database_url = f"sqlite:///{self.db_path}"
            
            # 创建引擎
            self.engine = create_engine(
                database_url,
                echo=self.echo,
                poolclass=StaticPool,
                connect_args={
                    "check_same_thread": False,  # SQLite特定配置
                    "timeout": 30  # 连接超时
                },
                pool_pre_ping=True  # 连接前检查
            )
            
            # 配置SQLite优化
            @event.listens_for(self.engine, "connect")
            def set_sqlite_pragma(dbapi_connection, connection_record):
                cursor = dbapi_connection.cursor()
                try:
                    # 启用外键约束
                    cursor.execute("PRAGMA foreign_keys=ON")

                    # 检测平台并设置合适的journal模式
                    import platform
                    system = platform.system().lower()

                    if system == "windows" or "microsoft" in platform.platform().lower():
                        # Windows平台使用DELETE模式，避免WAL锁定问题
                        cursor.execute("PRAGMA journal_mode=DELETE")
                        # Windows下使用较低的同步级别
                        cursor.execute("PRAGMA synchronous=OFF")
                        # 设置较短的超时时间
                        cursor.execute("PRAGMA busy_timeout=30000")  # 30秒
                    else:
                        # Linux/Mac平台使用WAL模式
                        cursor.execute("PRAGMA journal_mode=WAL")
                        cursor.execute("PRAGMA synchronous=NORMAL")

                    # 通用设置
                    cursor.execute("PRAGMA cache_size=10000")
                    cursor.execute("PRAGMA temp_store=MEMORY")

                except Exception as e:
                    print(f"警告: 设置SQLite PRAGMA失败: {e}")
                finally:
                    cursor.close()
            
            # 创建会话工厂
            self.SessionLocal = sessionmaker(
                autocommit=False,
                autoflush=False,
                bind=self.engine
            )
            
            # 创建所有表
            self.create_tables()
            
        except Exception as e:
            self.logger.error(f"数据库初始化失败: {e}")
            raise
    
    def create_tables(self) -> None:
        """创建所有数据表"""
        import time
        max_retries = 3
        retry_delay = 1  # 秒

        for attempt in range(max_retries):
            try:
                Base.metadata.create_all(bind=self.engine)
                if attempt > 0:
                    self.logger.info(f"数据表创建成功 (重试 {attempt} 次后)")
                return
            except Exception as e:
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    self.logger.warning(f"数据库被锁定，等待 {retry_delay} 秒后重试... (尝试 {attempt + 1}/{max_retries})")
                    time.sleep(retry_delay)
                    retry_delay *= 2  # 指数退避
                else:
                    self.logger.error(f"数据表创建失败: {e}")
                    raise
    
    def drop_tables(self) -> None:
        """删除所有数据表"""
        try:
            Base.metadata.drop_all(bind=self.engine)
            self.logger.info("数据表删除成功")
        except Exception as e:
            self.logger.error(f"数据表删除失败: {e}")
            raise
    
    def get_session(self) -> Session:
        """获取数据库会话"""
        if not self.SessionLocal:
            raise RuntimeError("数据库未初始化")
        return self.SessionLocal()
    
    def execute_sql(self, sql: str, params: Dict[str, Any] = None) -> Any:
        """执行原生SQL"""
        try:
            with self.get_session() as session:
                result = session.execute(sql, params or {})
                session.commit()
                return result
        except Exception as e:
            self.logger.error(f"SQL执行失败: {e}")
            raise
    
    def get_database_info(self) -> Dict[str, Any]:
        """获取数据库信息"""
        try:
            with self.get_session() as session:
                # 获取数据库文件大小
                db_size = os.path.getsize(self.db_path) if os.path.exists(self.db_path) else 0
                
                # 获取表信息
                tables_info = {}
                for table_name in Base.metadata.tables.keys():
                    count_sql = text(f"SELECT COUNT(*) FROM {table_name}")
                    result = session.execute(count_sql)
                    count = result.scalar()
                    tables_info[table_name] = count
                
                # 获取数据库版本
                version_result = session.execute(text("SELECT sqlite_version()"))
                sqlite_version = version_result.scalar()
                
                return {
                    "database_path": self.db_path,
                    "database_size_bytes": db_size,
                    "database_size_mb": db_size / (1024 * 1024),
                    "sqlite_version": sqlite_version,
                    "tables": tables_info,
                    "total_records": sum(tables_info.values())
                }
                
        except Exception as e:
            self.logger.error(f"获取数据库信息失败: {e}")
            return {"error": str(e)}
    
    def vacuum_database(self) -> None:
        """清理数据库（VACUUM操作）"""
        try:
            with self.engine.connect() as connection:
                connection.execute(text("VACUUM"))
            self.logger.info("数据库清理完成")
        except Exception as e:
            self.logger.error(f"数据库清理失败: {e}")
            raise
    
    def backup_database(self, backup_path: str) -> None:
        """备份数据库"""
        try:
            import shutil
            
            backup_path = Path(backup_path)
            backup_path.parent.mkdir(parents=True, exist_ok=True)
            
            # 复制数据库文件
            shutil.copy2(self.db_path, backup_path)
            
            self.logger.info(f"数据库备份完成: {backup_path}")
            
        except Exception as e:
            self.logger.error(f"数据库备份失败: {e}")
            raise
    
    def restore_database(self, backup_path: str) -> None:
        """恢复数据库"""
        try:
            import shutil
            
            if not os.path.exists(backup_path):
                raise FileNotFoundError(f"备份文件不存在: {backup_path}")
            
            # 关闭当前连接
            if self.engine:
                self.engine.dispose()
            
            # 恢复数据库文件
            shutil.copy2(backup_path, self.db_path)
            
            # 重新初始化数据库
            self._initialize_database()
            
            self.logger.info(f"数据库恢复完成: {backup_path}")
            
        except Exception as e:
            self.logger.error(f"数据库恢复失败: {e}")
            raise
    
    def optimize_database(self) -> None:
        """优化数据库性能"""
        try:
            with self.engine.connect() as connection:
                # 分析表统计信息
                connection.execute(text("ANALYZE"))

                # 重建索引
                connection.execute(text("REINDEX"))

            self.logger.info("数据库优化完成")

        except Exception as e:
            self.logger.error(f"数据库优化失败: {e}")
            raise
    
    def check_database_integrity(self) -> Dict[str, Any]:
        """检查数据库完整性"""
        try:
            with self.engine.connect() as connection:
                # 完整性检查
                integrity_result = connection.execute(text("PRAGMA integrity_check"))
                integrity_status = integrity_result.fetchall()

                # 外键检查
                foreign_key_result = connection.execute(text("PRAGMA foreign_key_check"))
                foreign_key_errors = foreign_key_result.fetchall()
                
                return {
                    "integrity_status": [row[0] for row in integrity_status],
                    "foreign_key_errors": [dict(row) for row in foreign_key_errors],
                    "is_healthy": len(integrity_status) == 1 and integrity_status[0][0] == "ok" and len(foreign_key_errors) == 0
                }
                
        except Exception as e:
            self.logger.error(f"数据库完整性检查失败: {e}")
            return {"error": str(e), "is_healthy": False}
    
    def close(self) -> None:
        """关闭数据库连接"""
        try:
            if self.engine:
                self.engine.dispose()
                self.logger.info("数据库连接已关闭")
        except Exception as e:
            self.logger.error(f"关闭数据库连接失败: {e}")


def get_database_manager() -> DatabaseManager:
    """获取全局数据库管理器实例"""
    global _database_manager
    if _database_manager is None:
        raise RuntimeError("数据库管理器未初始化")
    return _database_manager


def get_session() -> Session:
    """获取数据库会话的便捷函数"""
    return get_database_manager().get_session()
